/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.pdx.cs.guavadata;

import edu.pdx.cs.guavadata.data.Column;
import edu.pdx.cs.guavadata.data.Schema;
import edu.pdx.cs.guavadata.data.Scheme;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author VuTran
 */
public class DBConnection {

    private static String source;
    private static String catalog;
    private static Connection conn;
    private static String testCatalog;
    private static boolean useTest;
    private final static String initCatalog = "DumpDB";

    public static void initialize(String s, String c, String t) {
        useTest = false;
        source = s;
        catalog = c;
        testCatalog = t;
        String url = "jdbc:sqlserver://" + source + ":1433;databaseName=" + catalog;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, "sa", "");
/*
            if (conn != null) {
                conn.setCatalog("DumpDB");
                Statement stmt = conn.createStatement();
                stmt.executeUpdate("DROP DATABASE " + c);
                stmt.executeUpdate("CREATE DATABASE " + c);*/
            //    conn.setCatalog(c);
            //}
        } catch (Exception e) {
            System.err.println("ERROR DROP:" + e.toString());
            try {
                String newUrl = "jdbc:sqlserver://" + s + ":1433;databaseName=" + initCatalog;
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                conn = DriverManager.getConnection(newUrl, "sa", "");
                Statement stmt = conn.createStatement();
                stmt.executeUpdate("CREATE DATABASE " + c);
                conn.setCatalog(c);
            } catch (Exception ne) {
                System.err.println("ERROR: DumpDB access error. " + ne.toString());
            }
        }
    }

    public static void useTestCatalog(boolean b) {
        useTest = b;
    }

    public static void open() {
        try {
            if (!conn.isClosed()) {
                conn.close();
            }

            String url = "jdbc:sqlserver://" + source + ":1433;databaseName=" + catalog;
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, "sa", "");

            if (conn != null) {
                conn.setCatalog("DumpDB");
                Statement stmt = conn.createStatement();
                stmt.executeUpdate("DROP DATABASE " + catalog);
                stmt.executeUpdate("CREATE DATABASE " + catalog);
                conn.setCatalog(catalog);
            }

        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
    }

    public static void close() {
        try {
            conn.close();
        } catch (SQLException ex) {
            System.err.println("ERROR: " + ex.toString());
        }
    }

    public static ResultSet getColumns() {
        ResultSet rs = null;
        try {
           // open();
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getColumns(catalog, null, "dbo%" , null);
          //  close();
        } catch (SQLException ex) {
            System.err.println("ERROR: " + ex.toString());
        }

        return rs;
    }

    public static ResultSet getForeignKeys() {
        ResultSet rs = null;
        try {
          //  open();
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getExportedKeys(catalog, null, "*");
           // close();
        } catch (SQLException ex) {
            System.err.println("ERROR: " + ex.toString());
        }

        return rs;
    }

    public static ResultSet getTriggers() {
      //  open();
        ResultSet rs = executeQuery("SELECT name FROM sys.triggers");
      //  close();
        return rs;
    }

    /*
     * Create database schema from schema object
     * CRITICAL FUNCTION
     */
    public static void createSchema(Schema sc) {
        try {
           // open();

            DatabaseMetaData meta = conn.getMetaData();
            
            ResultSet rsTable = meta.getTables(catalog, null, null, null);
            List<String> lsTable = new ArrayList<String>();
            while (rsTable.next()) {
                lsTable.add(rsTable.getString("TABLE_NAME"));
            }

            PreparedStatement psAddColumn = conn.prepareStatement("ALTER TABLE ? ADD ? ?");
            Statement stmt = conn.createStatement();

            // loop through tables
            for (Scheme s : sc.tables) {


                if (lsTable.contains(s.getTableName())) {
                    ResultSet rsCol = meta.getColumns(catalog, null, s.getTableName(), null);
                    List<String> lsCol = new ArrayList<String>();
                    while (rsCol.next()) {
                        lsCol.add(rsCol.getString("COLUMN_NAME"));
                    }

                    // loop through columns
                    for (Column col : s.columns) {
                        if (lsCol.contains(col.getColumnName()))
                            continue;

                        // we have column that doesn't exist yet. Better make a DDL statment
                        psAddColumn.setString(1, s.getTableName());
                        psAddColumn.setString(2, col.getColumnName());
                        psAddColumn.setString(3, col.dom.getDBType());
                        psAddColumn.execute();
                    }
                    continue;
                }
                stmt.execute(s.toString());
                lsTable.add(s.getTableName());
            }

           // close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static int executeNonQuery(String s) {
        int i = 0;
        try {
            if ((s == null) || (s.equals(""))) {
                return 0;
            }
           // open();
            Statement stmt = conn.createStatement();
            i = stmt.executeUpdate(s);
           // close();
        } catch (SQLException ex) {
            System.err.println("ERROR: " + ex.toString());
        }

        return i;
    }

    public static ResultSet executeQuery(String s) {
        ResultSet rs = null;
       // open();
        try {
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery(s);
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
        //close();
        return rs;
    }
}
